<?php

class SiteDataTweetsFinder
{
  protected $_pdo;
    
  public function __construct()
  {   
    $this->_pdo = PDOFactory::getInstance();
  }
  
  public function findNotExisted()
  { 
    $query = $this->_pdo->query(
      'SELECT `sites_data_tweets`.*, `sites`.*, "0" AS `tweetsAmount` FROM `sites` '.
      ' LEFT JOIN `sites_data_tweets` ON `sites_data_tweets`.`idSite` = `sites`.`idSite`'.
      ' WHERE `sites`.`findTweets` > 0 AND `sites_data_tweets`.`idSitesDataTweet` IS NULL '
      );
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $query->execute();    
    $count = 0;
    foreach( $query as $site ) {
      echo $site['name']." (idSite: ".$site['idSite'].")\n";
      $this->_createData($site);
      $count++;
    }
    return $count;
  }
  
  public function findOutdated()
  { 
    $query = $this->_pdo->query(
'SELECT `tweets`.*, `sites`.*, COUNT(`tweets`.`idSitesDataTweet`) AS "tweetsAmount"
FROM `sites`
LEFT JOIN (SELECT * FROM `sites_data_tweets` WHERE `lastUpdated` > DATE_FORMAT(NOW(), "%Y-%m-%d 00:00:00") ORDER BY `sites_data_tweets`.`lastUpdated` DESC) `tweets` ON `tweets`.`idSite` = `sites`.`idSite`
GROUP BY `sites`.`idSite`
HAVING COUNT(`tweets`.`idSitesDataTweet`) < `sites`.`findTweets`'
      );
    $query->setFetchMode(PDO::FETCH_ASSOC);
    $query->execute();    
    $count = 0;
    foreach( $query as $site ) {
      echo $site['name']." (idSite: ".$site['idSite'].")\n";
      $this->_createData($site);
      $count++;
    }
    return $count;
  }  

  protected function _createData($site)
  {    
    $amount = $site['findTweets'];
    $i = $site['tweetsAmount'];
    $t = $amount-$i;
    $twitter = new TwitterSearch($site['name']);
    $params = array('tweets'=>$t);
    if( $site['idTweet'] ) {
      $params['since_id'] = $site['idTweet'];
    }
    $tweets = $twitter->getTweets($params);

    foreach( $tweets as $tweet ) {
      $set = array(
        'idSite' => $site['idSite'],
        'idTweet' => $tweet['id'],
        'author_uri' => $tweet['author_uri'],
        'author_name' => $tweet['author_name'],
        'title' => $tweet['title'],
        'href' => $tweet['href']
        );
      $sql = 'INSERT INTO `sites_data_tweets` (';
      foreach( array_keys($set) as $key ) {
        $sql .= ' `'.$key.'`,';
      }
      $sql = rtrim($sql,',');
      $sql .= ') VALUES (';
      foreach( array_keys($set) as $key ) {
        $sql .= ' :'.$key.',';
      }
      $sql = rtrim($sql,',');
      $sql .= ')';
      $state = $this->_pdo->prepare($sql);
      foreach( $set as $key => $v ) {
        if( is_string($v) ) {
          $state->bindValue(':'.$key,$v,PDO::PARAM_STR);
        } else {
          $state->bindValue(':'.$key,$v,PDO::PARAM_INT);
        }
      }
      $state->execute();      
    }
  }
}